Combining Conditions with AND, OR, and NOT in MySQL – Detailed Notes

Course: MySQL Training

Topic: Combining Conditions with AND, OR, and NOT

Course Link: MySQL Training Course

Demo Link: Register for a Free Course Demo

---

1. Introduction to Combining Conditions in MySQL

In MySQL, the WHERE clause is used to filter records based on specified conditions. Sometimes, a single condition is not enough to retrieve the required information. We may need to check multiple conditions simultaneously or exclude records that match a particular condition.

MySQL provides three important logical operators for combining and modifying conditions:

• AND: Returns records when all specified conditions are true.
• OR: Returns records when at least one specified condition is true.
• NOT: Reverses a condition and selects records for which that condition is false.

These operators help users create precise queries and retrieve only the data required for reporting, analysis, and application development.

For example, an organization may want to find employees who work in the IT department and earn more than ₹50,000, customers who live in Mumbai or Pune, or products that are not currently available.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand logical operators in MySQL.
• Use AND to combine multiple conditions.
• Use OR to match alternative conditions.
• Use NOT to negate a condition.
• Combine AND, OR, and NOT in a single query.
• Understand operator precedence and the role of parentheses.
• Apply logical operators with comparison operators, LIKE, IN, BETWEEN, and IS NULL.
• Identify common errors in queries involving multiple conditions.
• Write practical queries for filtering employee, customer, order, and product data.

3. Understanding Logical Operators

Logical operators evaluate conditions and determine whether a record satisfies the filtering criteria. They are commonly used with the WHERE clause in a SELECT statement.

Operator	Purpose	Condition Result	Example
AND	Combines conditions that must all be true.	True only when every condition is true.	salary > 50000 AND department = 'IT'
OR	Combines alternative conditions.	True when at least one condition is true.	city = 'Mumbai' OR city = 'Pune'
NOT	Negates a condition.	Reverses true and false; NULL requires special consideration.	NOT (salary > 50000)

4. Sample Database for Practical Examples

Throughout these notes, we will use an employees table to understand how AND, OR, and NOT work.

4.1 Creating the Database

CREATE DATABASE company_db;
USE company_db;

4.2 Creating the Employees Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    department VARCHAR(50),
    city VARCHAR(50),
    salary DECIMAL(10,2),
    experience INT,
    status VARCHAR(20)
);

4.3 Inserting Sample Records

INSERT INTO employees
(employee_id, employee_name, department, city, salary, experience, status)
VALUES
(1, 'Amit Sharma', 'IT', 'Mumbai', 65000, 5, 'Active'),
(2, 'Priya Verma', 'HR', 'Pune', 48000, 3, 'Active'),
(3, 'Rahul Mehta', 'IT', 'Delhi', 75000, 7, 'Active'),
(4, 'Sneha Patil', 'Finance', 'Mumbai', 55000, 4, 'Inactive'),
(5, 'Vikas Singh', 'Sales', 'Pune', 42000, 2, 'Active'),
(6, 'Neha Joshi', 'IT', 'Mumbai', 52000, 3, 'Active'),
(7, 'Karan Shah', 'HR', 'Delhi', 60000, 6, 'Inactive'),
(8, 'Pooja Desai', 'Finance', 'Pune', 70000, 8, 'Active'),
(9, 'Rohan Kulkarni', 'Sales', 'Mumbai', 45000, 2, 'Inactive'),
(10, 'Ananya Rao', 'IT', 'Pune', 80000, 9, 'Active');

4.4 Viewing the Sample Data

SELECT * FROM employees;

This query displays all employee records. In the following sections, we will use logical operators to retrieve specific subsets of this data.

5. Using the AND Operator in MySQL

5.1 What Is AND?

The AND operator combines two or more conditions. A record is selected only when every condition connected by AND evaluates to true.

For example, an employee must belong to the IT department and have a salary greater than ₹50,000 to appear in the result.

5.2 Syntax of AND

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2;

For three or more conditions, additional AND operators can be used.

SELECT column1, column2, ...
FROM table_name
WHERE condition1
  AND condition2
  AND condition3;

5.3 Example: Employees in the IT Department Earning More Than ₹50,000

SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
  AND salary > 50000;

Explanation:

• department = 'IT' checks whether the employee belongs to the IT department.
• salary > 50000 checks whether the employee earns more than ₹50,000.
• AND requires both conditions to be true.

Expected matching employees: Amit Sharma, Rahul Mehta, Neha Joshi, and Ananya Rao.

5.4 Example: Employees from Mumbai with Active Status

SELECT employee_name, city, status
FROM employees
WHERE city = 'Mumbai'
  AND status = 'Active';

This query returns employees who are located in Mumbai and whose status is Active. An employee who meets only one condition will not be included.

5.5 Example: Multiple AND Conditions

SELECT employee_name, department, salary, experience
FROM employees
WHERE department = 'IT'
  AND salary > 50000
  AND experience >= 5;

This query retrieves employees who work in IT, earn more than ₹50,000, and have at least five years of experience. All three conditions must be satisfied.

5.6 Example: Employees from a Particular City with a Salary Range

SELECT employee_name, city, salary
FROM employees
WHERE city = 'Pune'
  AND salary >= 45000
  AND salary <= 80000;

This query selects employees based in Pune whose salaries fall between ₹45,000 and ₹80,000, including both boundaries.

5.7 Truth Table for AND

Condition A	Condition B	A AND B
TRUE	TRUE	TRUE
TRUE	FALSE	FALSE
FALSE	TRUE	FALSE
FALSE	FALSE	FALSE

Key point: AND is useful when records must satisfy every specified requirement.

6. Using the OR Operator in MySQL

6.1 What Is OR?

The OR operator combines alternative conditions. A record is selected if at least one condition evaluates to true.

For example, if a company wants to find employees from either Mumbai or Pune, OR can be used to check both city values.

6.2 Syntax of OR

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2;

6.3 Example: Employees from Mumbai or Pune

SELECT employee_name, city
FROM employees
WHERE city = 'Mumbai'
   OR city = 'Pune';

This query returns employees whose city is Mumbai or Pune. Employees from Delhi do not match either condition and are excluded.

6.4 Example: Employees in IT or HR

SELECT employee_name, department
FROM employees
WHERE department = 'IT'
   OR department = 'HR';

The query returns employees who belong to either the IT department or the HR department.

6.5 Example: Employees with a High Salary or Extensive Experience

SELECT employee_name, salary, experience
FROM employees
WHERE salary >= 70000
   OR experience >= 7;

An employee appears in the result if their salary is at least ₹70,000, their experience is at least seven years, or both conditions are true.

6.6 Example: Combining OR with Another Condition

SELECT employee_name, department, city, status
FROM employees
WHERE (department = 'IT' OR department = 'HR')
  AND status = 'Active';

This query selects active employees who belong to either IT or HR. The parentheses group the two department alternatives together before the active-status requirement is applied.

6.7 Truth Table for OR

Condition A	Condition B	A OR B
TRUE	TRUE	TRUE
TRUE	FALSE	TRUE
FALSE	TRUE	TRUE
FALSE	FALSE	FALSE

Key point: OR is useful when a record may qualify through one or more alternative conditions.

7. Using the NOT Operator in MySQL

7.1 What Is NOT?

The NOT operator reverses a logical condition. It is commonly used to exclude records that satisfy a particular condition.

For example, a business may need to retrieve employees who are not in the IT department or products that are not active.

7.2 Syntax of NOT

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

7.3 Example: Employees Not in the IT Department

SELECT employee_name, department
FROM employees
WHERE NOT department = 'IT';

This query returns employees whose department is not IT, provided the department value is not NULL.

The following form is also commonly used:

SELECT employee_name, department
FROM employees
WHERE department <> 'IT';

In MySQL, <> is the standard SQL not-equal operator. It is equivalent to != in MySQL.

7.4 Example: Employees Who Are Not Inactive

SELECT employee_name, status
FROM employees
WHERE NOT status = 'Inactive';

This query returns employees whose status is not Inactive. Because SQL uses three-valued logic, records with a NULL status will not pass this condition.

7.5 Example: Using NOT with IN

SELECT employee_name, city
FROM employees
WHERE city NOT IN ('Mumbai', 'Pune');

This query excludes employees from Mumbai and Pune. Employees with a NULL city are also excluded because the comparison does not evaluate to true for those records.

7.6 Example: Using NOT with LIKE

SELECT employee_name
FROM employees
WHERE employee_name NOT LIKE 'A%';

The pattern 'A%' matches names beginning with A. Adding NOT LIKE excludes names matching that pattern.

7.7 Example: Using NOT with BETWEEN

SELECT employee_name, salary
FROM employees
WHERE salary NOT BETWEEN 50000 AND 70000;

This query returns employees whose salaries are below ₹50,000 or above ₹70,000. The values ₹50,000 and ₹70,000 are included in the BETWEEN range and are therefore excluded by NOT BETWEEN.

7.8 Truth Table for NOT

Condition A	NOT A
TRUE	FALSE
FALSE	TRUE
UNKNOWN (NULL-related result)	UNKNOWN

Key point: NOT reverses TRUE and FALSE, but it does not convert UNKNOWN into TRUE. For checking missing values, use IS NULL or IS NOT NULL.

8. Difference Between AND, OR, and NOT

Feature	AND	OR	NOT
Main purpose	Requires all conditions to be true.	Accepts at least one true condition.	Negates a condition.
Conditions	Combines two or more conditions.	Combines two or more alternatives.	Usually applies to one condition or a grouped expression.
Typical use	Department is IT and salary exceeds a threshold.	City is Mumbai or Pune.	Department is not IT.
Result when one condition is false	AND evaluates to false if another condition is false.	OR may still be true if another condition is true.	Depends on the condition being negated.

9. Combining AND and OR in the Same Query

Real-world database queries often require multiple conditions and alternative choices. MySQL allows AND and OR to be combined in the same WHERE clause.

9.1 Example: IT Employees in Mumbai or Pune

SELECT employee_name, department, city
FROM employees
WHERE department = 'IT'
  AND (city = 'Mumbai' OR city = 'Pune');

The query returns employees who work in IT and are located in either Mumbai or Pune. The parentheses make the city alternatives explicit.

9.2 Example: High-Salary Employees in IT or HR

SELECT employee_name, department, salary
FROM employees
WHERE (department = 'IT' OR department = 'HR')
  AND salary > 55000;

This query returns employees in IT or HR who earn more than ₹55,000. The salary condition must be true regardless of which department matches.

9.3 Example: Active Employees from Mumbai or Pune Earning at Least ₹50,000

SELECT employee_name, city, salary, status
FROM employees
WHERE status = 'Active'
  AND (city = 'Mumbai' OR city = 'Pune')
  AND salary >= 50000;

The query requires three things: the employee must be active, the city must be Mumbai or Pune, and the salary must be at least ₹50,000.

9.4 Example: Employees with Specific Departments and Experience

SELECT employee_name, department, experience
FROM employees
WHERE (department = 'IT' OR department = 'Finance')
  AND experience >= 4;

This retrieves employees in IT or Finance who have at least four years of experience.

10. Combining AND and NOT

AND can be used with NOT to enforce multiple requirements while excluding a specific group of records.

10.1 Example: Active Employees Who Are Not in HR

SELECT employee_name, department, status
FROM employees
WHERE status = 'Active'
  AND NOT (department = 'HR');

This query selects active employees and excludes those whose department is HR.

10.2 Example: Employees Earning More Than ₹50,000 but Not in Finance

SELECT employee_name, department, salary
FROM employees
WHERE salary > 50000
  AND NOT (department = 'Finance');

The query requires a salary greater than ₹50,000 and excludes Finance employees.

10.3 Example: Employees Who Are Active and Not Based in Delhi

SELECT employee_name, city, status
FROM employees
WHERE status = 'Active'
  AND NOT (city = 'Delhi');

This query retrieves active employees whose city is not Delhi. Records with a NULL city do not pass the negated comparison.

11. Combining OR and NOT

OR can combine alternative conditions while NOT excludes a particular value or group.

11.1 Example: Employees in IT or HR but Not Inactive

SELECT employee_name, department, status
FROM employees
WHERE (department = 'IT' OR department = 'HR')
  AND NOT (status = 'Inactive');

The query selects employees from IT or HR whose status is not Inactive. A NULL status will not satisfy the condition.

11.2 Example: Employees from Mumbai or Pune, Excluding Sales

SELECT employee_name, department, city
FROM employees
WHERE (city = 'Mumbai' OR city = 'Pune')
  AND NOT (department = 'Sales');

This query returns employees in Mumbai or Pune who do not belong to Sales.

11.3 Example: Excluding Multiple Departments

SELECT employee_name, department
FROM employees
WHERE NOT (department = 'HR' OR department = 'Sales');

This query excludes both HR and Sales employees. It can also be written as:

SELECT employee_name, department
FROM employees
WHERE department <> 'HR'
  AND department <> 'Sales';

For non-NULL department values, these expressions produce equivalent filtering results.

12. Understanding Operator Precedence

Operator precedence determines the order in which MySQL evaluates logical operators when parentheses are not used.

In a WHERE expression, NOT has higher precedence than AND, and AND has higher precedence than OR.

Priority	Operator	Evaluation Order
1	NOT	Evaluated before AND and OR.
2	AND	Evaluated before OR.
3	OR	Evaluated after AND.

12.1 Example Without Parentheses

SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
   OR department = 'HR'
  AND salary > 55000;

MySQL evaluates AND before OR. The expression is therefore interpreted as:

WHERE department = 'IT'
   OR (department = 'HR' AND salary > 55000);

This means all IT employees can match regardless of salary, while HR employees must earn more than ₹55,000.

12.2 Example With Parentheses

SELECT employee_name, department, salary
FROM employees
WHERE (department = 'IT' OR department = 'HR')
  AND salary > 55000;

Here, the department alternatives are evaluated together. The salary condition then applies to both departments.

12.3 Why Parentheses Are Important

• They make query logic easier to read.
• They reduce confusion when multiple operators are used.
• They help prevent accidentally including unwanted records.
• They make complex business rules easier to verify and maintain.

Best practice: Use parentheses whenever a query combines AND and OR, even when you already understand the precedence rules.

13. Using Logical Operators with Comparison Operators

Logical operators are frequently combined with comparison operators such as =, <>, >, <, >=, and <=.

13.1 Example: Salary Greater Than ₹50,000 and Experience at Least 3 Years

SELECT employee_name, salary, experience
FROM employees
WHERE salary > 50000
  AND experience >= 3;

13.2 Example: Employees with a Salary Below ₹45,000 or Above ₹70,000

SELECT employee_name, salary
FROM employees
WHERE salary < 45000
   OR salary > 70000;

13.3 Example: Employees Who Do Not Earn Less Than ₹50,000

SELECT employee_name, salary
FROM employees
WHERE NOT (salary < 50000);

For non-NULL salary values, this returns employees whose salary is at least ₹50,000. A NULL salary does not satisfy the negated comparison.

14. Using Logical Operators with IN and NOT IN

The IN operator checks whether a value matches any value in a specified list. It can simplify conditions that would otherwise require several OR operators.

14.1 Using OR

SELECT employee_name, city
FROM employees
WHERE city = 'Mumbai'
   OR city = 'Pune'
   OR city = 'Delhi';

14.2 Using IN

SELECT employee_name, city
FROM employees
WHERE city IN ('Mumbai', 'Pune', 'Delhi');

These queries are equivalent for the listed non-NULL city values. The IN version is shorter and easier to maintain.

14.3 Using NOT IN with AND Conditions

SELECT employee_name, department, salary
FROM employees
WHERE department NOT IN ('HR', 'Sales')
  AND salary >= 50000;

This query excludes HR and Sales employees and selects the remaining employees who earn at least ₹50,000.

Important: Be careful when using NOT IN with a subquery. If the subquery returns NULL, the result can be UNKNOWN for comparisons that would otherwise seem to match no listed value. Use a subquery that excludes NULL values or consider NOT EXISTS where appropriate.

15. Using Logical Operators with BETWEEN

The BETWEEN operator checks whether a value lies within an inclusive range. AND, OR, and NOT can be used to extend range-based filtering.

15.1 Example: Salary Between ₹50,000 and ₹70,000

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 70000;

Both ₹50,000 and ₹70,000 are included in the range.

15.2 Example: Salary Outside a Range

SELECT employee_name, salary
FROM employees
WHERE NOT (salary BETWEEN 50000 AND 70000);

This returns non-NULL salaries below ₹50,000 or above ₹70,000.

15.3 Example: Employees with a Salary Range and Active Status

SELECT employee_name, salary, status
FROM employees
WHERE salary BETWEEN 45000 AND 80000
  AND status = 'Active';

The query retrieves active employees whose salaries fall within the specified range.

16. Using Logical Operators with LIKE

The LIKE operator searches text using patterns. The percent sign (%) matches zero or more characters, while the underscore (_) matches a single character.

16.1 Example: Names Beginning with A or P

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%'
   OR employee_name LIKE 'P%';

This query returns names that begin with A or P.

16.2 Example: Names Beginning with A but Not in the Sales Department

SELECT employee_name, department
FROM employees
WHERE employee_name LIKE 'A%'
  AND NOT (department = 'Sales');

The query selects employees whose names begin with A and whose department is not Sales.

16.3 Example: Names That Do Not Begin with R

SELECT employee_name
FROM employees
WHERE employee_name NOT LIKE 'R%';

This query excludes names that match the pattern beginning with R. Case sensitivity depends on the column collation and comparison settings.

17. Handling NULL Values with Logical Operators

In MySQL, NULL represents a missing or unknown value. It is not the same as zero, an empty string, or the word 'NULL'. Comparisons involving NULL often produce UNKNOWN rather than TRUE or FALSE.

17.1 Why Equality Does Not Work for NULL

The following query does not correctly find rows with missing department values:

SELECT employee_name, department
FROM employees
WHERE department = NULL;

Use IS NULL instead:

SELECT employee_name, department
FROM employees
WHERE department IS NULL;

17.2 Finding Records with Non-NULL Values

SELECT employee_name, department
FROM employees
WHERE department IS NOT NULL;

17.3 Combining NULL Checks with AND

SELECT employee_name, department, salary
FROM employees
WHERE department IS NOT NULL
  AND salary > 50000;

This query selects employees whose department is known and whose salary is greater than ₹50,000.

17.4 Combining NULL Checks with OR

SELECT employee_name, department
FROM employees
WHERE department IS NULL
   OR department = 'IT';

This query returns employees whose department is missing or whose department is IT.

17.5 Three-Valued Logic in SQL

SQL conditions can produce TRUE, FALSE, or UNKNOWN. WHERE returns a row only when the complete condition evaluates to TRUE.

Expression	Result	Explanation
NULL = NULL	UNKNOWN	Equality comparisons with NULL do not establish that the values are equal.
NULL > 100	UNKNOWN	The comparison value is unknown.
NOT (NULL = 100)	UNKNOWN	NOT does not turn UNKNOWN into TRUE.
NULL IS NULL	TRUE	IS NULL explicitly checks for a missing value.

18. Practical Business Examples

18.1 Employee Management

Requirement: Find active IT employees who earn at least ₹60,000.

SELECT employee_name, department, salary, status
FROM employees
WHERE department = 'IT'
  AND salary >= 60000
  AND status = 'Active';

18.2 Customer Management

Assume a table named customers contains customer names, cities, and account statuses.

SELECT customer_name, city, status
FROM customers
WHERE (city = 'Mumbai' OR city = 'Pune')
  AND status = 'Active';

This query finds active customers located in Mumbai or Pune.

18.3 Product Management

Assume a table named products contains product names, prices, and availability status.

SELECT product_name, price, status
FROM products
WHERE price >= 1000
  AND NOT (status = 'Discontinued');

This query retrieves products priced at ₹1,000 or more that are not marked as discontinued. If status can be NULL, those records will not match this condition.

18.4 Sales Analysis

Assume an orders table contains order IDs, order amounts, regions, and order statuses.

SELECT order_id, region, amount, status
FROM orders
WHERE (region = 'West' OR region = 'South')
  AND amount > 10000
  AND status = 'Completed';

This query retrieves completed orders from the West or South region with an amount greater than ₹10,000.

18.5 Student Management

Assume a students table contains student names, marks, attendance percentages, and results.

SELECT student_name, marks, attendance, result
FROM students
WHERE marks >= 75
  AND attendance >= 80
  AND NOT (result = 'Disqualified');

This query selects students with marks of at least 75, attendance of at least 80%, and a result other than Disqualified. NULL result values will not satisfy the NOT condition.

19. Common Mistakes and How to Fix Them

19.1 Forgetting Parentheses

Incorrect or potentially unintended:

SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
   OR department = 'HR'
  AND salary > 55000;

Improved when the salary requirement applies to both departments:

SELECT employee_name, department, salary
FROM employees
WHERE (department = 'IT' OR department = 'HR')
  AND salary > 55000;

19.2 Using AND When OR Is Required

Incorrect for finding either city:

SELECT employee_name, city
FROM employees
WHERE city = 'Mumbai'
  AND city = 'Pune';

A single city value cannot normally be both Mumbai and Pune at the same time.

Correct:

SELECT employee_name, city
FROM employees
WHERE city = 'Mumbai'
   OR city = 'Pune';

19.3 Using OR When AND Is Required

If the requirement says employees must belong to IT and earn more than ₹50,000, OR is incorrect because it also includes employees satisfying only one condition.

Correct:

SELECT employee_name, salary
FROM employees
WHERE department = 'IT'
  AND salary > 50000;

19.4 Incorrect NULL Comparisons

Incorrect:

SELECT employee_name
FROM employees
WHERE department = NULL;

Correct:

SELECT employee_name
FROM employees
WHERE department IS NULL;

19.5 Incorrect Use of NOT IN with NULL

When a NOT IN list or subquery contains NULL, comparisons may evaluate to UNKNOWN and unexpectedly return no matching rows.

Safer approach for a subquery:

SELECT c.customer_id, c.customer_name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_customers AS b
    WHERE b.customer_id = c.customer_id
);

NOT EXISTS checks that no matching row is present in the subquery. If NULL values are possible in the matching columns, review the intended business rules carefully.

19.6 Forgetting Quotes Around Text Values

Incorrect:

SELECT employee_name
FROM employees
WHERE department = IT;

Correct:

SELECT employee_name
FROM employees
WHERE department = 'IT';

Text literals should normally be enclosed in single quotes. Numeric values are generally written without quotes when compared to numeric columns.

19.7 Using Too Many Conditions Without Formatting

Long WHERE clauses can become difficult to understand when all conditions are written on one line.

Recommended formatting:

SELECT employee_name, department, city, salary
FROM employees
WHERE status = 'Active'
  AND (department = 'IT' OR department = 'Finance')
  AND (city = 'Mumbai' OR city = 'Pune')
  AND salary >= 50000;

Placing each condition on a separate line makes the query easier to review and debug.

20. Best Practices for Using AND, OR, and NOT

• Use AND when every condition must be satisfied.
• Use OR when any one of several alternatives is sufficient.
• Use NOT to exclude a condition or negate a logical expression.
• Use parentheses to group related conditions, especially when combining AND and OR.
• Use IN when comparing one column against multiple possible values.
• Use BETWEEN for inclusive ranges and NOT BETWEEN to exclude a range.
• Use IS NULL and IS NOT NULL to check missing values.
• Be careful with NOT IN when a list or subquery can contain NULL.
• Use clear indentation and line breaks in complex queries.
• Test queries with SELECT before using similar conditions in UPDATE or DELETE statements.
• Use meaningful column names and qualify them with table aliases when working with multiple tables.
• Review the query results to ensure the conditions reflect the intended business requirement.
• Consider indexes on columns frequently used in filtering, while remembering that index effectiveness depends on the query and data distribution.
• Use EXPLAIN when you need to understand how MySQL plans to execute a complex query.

21. Performance Considerations

Logical operators are part of the filtering logic, but query performance also depends on table size, indexes, data distribution, and the query execution plan.

21.1 Indexes on Filtered Columns

If queries frequently filter employees by department, status, or city, indexes may improve performance for suitable workloads.

CREATE INDEX idx_employees_department
ON employees (department);

CREATE INDEX idx_employees_status
ON employees (status);

Indexes consume storage and can increase the cost of data modifications. Create them based on actual query patterns rather than automatically indexing every column.

21.2 Understanding Query Execution

The EXPLAIN statement helps inspect the execution plan chosen by MySQL.

EXPLAIN
SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
  AND salary > 50000;

Review the plan to understand access methods, possible indexes, and estimated rows examined. Do not assume that using AND or OR alone makes a query fast or slow.

21.3 Avoid Unnecessary Conditions

Conditions should reflect the actual requirement. Extra conditions can make queries harder to maintain and may exclude valid records accidentally.

22. AND, OR, and NOT with UPDATE and DELETE

Logical operators can also control which rows are modified or deleted. These operations should be handled carefully because an incorrect WHERE clause can affect many records.

22.1 Example: Updating Selected Employees

UPDATE employees
SET status = 'Inactive'
WHERE department = 'Sales'
  AND city = 'Mumbai';

This updates employees in Sales who are located in Mumbai.

22.2 Example: Deleting Records Matching Alternative Conditions

DELETE FROM employees
WHERE status = 'Inactive'
  AND (department = 'Sales' OR department = 'HR');

This deletes inactive employees who belong to Sales or HR. Run a matching SELECT first to inspect the affected records.

SELECT employee_id, employee_name, department, status
FROM employees
WHERE status = 'Inactive'
  AND (department = 'Sales' OR department = 'HR');

Safety reminder: Before running UPDATE or DELETE, verify the WHERE clause and consider using a transaction when the storage engine and environment support it.

23. Step-by-Step Method to Build a Query

1. Understand the requirement: Identify exactly which records should be returned.
2. Choose the columns: Select only the information needed in the output.
3. Identify individual conditions: Write each requirement as a separate comparison.
4. Choose the logical operator: Use AND for requirements that must all hold, OR for alternatives, and NOT for exclusions.
5. Group conditions: Add parentheses to show which alternatives belong together.
6. Consider NULL values: Decide how missing values should be handled.
7. Execute the SELECT query: Inspect the returned rows.
8. Validate the results: Confirm that no valid records are missing and no unintended records appear.
9. Review performance: Use EXPLAIN when query complexity or table size makes it useful.

24. Practice Exercises

Use the sample employees table to solve the following exercises.

1. Display employees who work in IT and earn more than ₹60,000.
2. Display employees who are located in Mumbai or Delhi.
3. Display employees who are not in the HR department.
4. Display active employees who work in Finance.
5. Display employees who earn at least ₹50,000 and have at least three years of experience.
6. Display employees who work in IT or Finance and earn more than ₹55,000.
7. Display employees who are active and are not located in Delhi.
8. Display employees whose salary is below ₹45,000 or above ₹70,000.
9. Display employees who are not in HR or Sales.
10. Display active employees from Mumbai or Pune whose salary is at least ₹50,000.
11. Display employees whose names begin with A or R.
12. Display employees whose salary is not between ₹50,000 and ₹70,000.

25. Practice Exercise Solutions

Exercise 1: IT Employees Earning More Than ₹60,000

SELECT employee_name, department, salary
FROM employees
WHERE department = 'IT'
  AND salary > 60000;

Exercise 2: Employees in Mumbai or Delhi

SELECT employee_name, city
FROM employees
WHERE city = 'Mumbai'
   OR city = 'Delhi';

Exercise 3: Employees Not in HR

SELECT employee_name, department
FROM employees
WHERE NOT (department = 'HR');

Exercise 4: Active Finance Employees

SELECT employee_name, department, status
FROM employees
WHERE department = 'Finance'
  AND status = 'Active';

Exercise 5: Salary and Experience Requirements

SELECT employee_name, salary, experience
FROM employees
WHERE salary >= 50000
  AND experience >= 3;

Exercise 6: IT or Finance Employees Earning More Than ₹55,000

SELECT employee_name, department, salary
FROM employees
WHERE (department = 'IT' OR department = 'Finance')
  AND salary > 55000;

Exercise 7: Active Employees Not Located in Delhi

SELECT employee_name, city, status
FROM employees
WHERE status = 'Active'
  AND NOT (city = 'Delhi');

Exercise 8: Salaries Outside the Specified Limits

SELECT employee_name, salary
FROM employees
WHERE salary < 45000
   OR salary > 70000;

Exercise 9: Employees Not in HR or Sales

SELECT employee_name, department
FROM employees
WHERE department NOT IN ('HR', 'Sales');

Exercise 10: Active Employees in Mumbai or Pune Earning at Least ₹50,000

SELECT employee_name, city, salary, status
FROM employees
WHERE status = 'Active'
  AND (city = 'Mumbai' OR city = 'Pune')
  AND salary >= 50000;

Exercise 11: Names Beginning with A or R

SELECT employee_name
FROM employees
WHERE employee_name LIKE 'A%'
   OR employee_name LIKE 'R%';

Exercise 12: Salary Not Between ₹50,000 and ₹70,000

SELECT employee_name, salary
FROM employees
WHERE salary NOT BETWEEN 50000 AND 70000;

26. Mini Project: Employee Filtering and Reporting

Project Objective

Create a collection of MySQL queries that use AND, OR, and NOT to generate employee reports based on department, location, salary, experience, and status.

Project Requirements

• Find active employees who earn more than ₹50,000.
• Find employees from Mumbai or Pune.
• Find employees in IT or Finance with at least four years of experience.
• Exclude inactive employees from a report.
• Find employees who earn between ₹45,000 and ₹75,000 and are not in Sales.
• Identify records with missing department values if such records exist.
• Compare query results before and after adding parentheses to a mixed AND/OR expression.

Sample Project Query

SELECT
    employee_id,
    employee_name,
    department,
    city,
    salary,
    experience,
    status
FROM employees
WHERE status = 'Active'
  AND (department = 'IT' OR department = 'Finance')
  AND (city = 'Mumbai' OR city = 'Pune')
  AND salary BETWEEN 45000 AND 80000
  AND experience >= 3
ORDER BY salary DESC;

Expected Learning Outcome

This project demonstrates how to combine several logical requirements, group alternative conditions, filter numeric ranges, exclude unwanted records, and sort the resulting dataset for reporting.

27. Interview Questions and Answers

Q1. What are logical operators in MySQL?

Logical operators combine or modify conditions in SQL queries. Common logical operators include AND, OR, and NOT.

Q2. What is the purpose of AND?

AND returns records only when every connected condition evaluates to TRUE.

Q3. What is the purpose of OR?

OR returns records when at least one connected condition evaluates to TRUE.

Q4. What does NOT do in MySQL?

NOT negates a condition. It changes TRUE to FALSE and FALSE to TRUE, while UNKNOWN remains UNKNOWN.

Q5. What is the difference between AND and OR?

AND requires all conditions to be true, whereas OR requires at least one condition to be true.

Q6. Can AND and OR be used together?

Yes. They can be combined in a WHERE clause. Parentheses should be used to make the intended grouping clear.

Q7. Which operator has higher precedence, AND or OR?

AND has higher precedence than OR. Therefore, AND is evaluated before OR unless parentheses specify a different grouping.

Q8. Why should parentheses be used in complex conditions?

Parentheses make logical grouping explicit, improve readability, and help avoid filtering records incorrectly.

Q9. How can multiple alternative values be checked more simply?

The IN operator can replace several OR comparisons on the same column. For example, city IN ('Mumbai', 'Pune').

Q10. How can a condition be negated?

Use NOT before a condition or use an appropriate negative operator, such as <>, NOT IN, NOT LIKE, or NOT BETWEEN.

Q11. How do you check whether a column contains NULL?

Use IS NULL to find missing values and IS NOT NULL to find non-NULL values.

Q12. Why does WHERE column_name = NULL not work as expected?

Comparisons with NULL evaluate to UNKNOWN rather than TRUE. Use column_name IS NULL.

Q13. What is the result of TRUE AND FALSE?

The result is FALSE because both conditions must be true for AND to return TRUE.

Q14. What is the result of TRUE OR FALSE?

The result is TRUE because at least one condition is true.

Q15. What happens when NOT is applied to UNKNOWN?

The result remains UNKNOWN. As a result, a WHERE clause using that expression will not select the row.

Q16. What is the difference between NOT IN and NOT EXISTS?

NOT IN compares a value against a list or subquery result and can be affected by NULL values. NOT EXISTS checks whether a matching row exists and is often useful for exclusion queries involving subqueries.

Q17. Can logical operators be used with UPDATE and DELETE?

Yes. They can filter which records are modified or deleted. The WHERE clause should be tested carefully before executing these statements.

Q18. How can query performance be investigated?

Review appropriate indexes and use EXPLAIN to inspect the execution plan. Actual performance depends on the query, data, indexes, and database configuration.

28. Quick Revision Notes

• AND means all conditions must be true.
• OR means at least one condition must be true.
• NOT reverses a condition.
• AND has higher precedence than OR.
• NOT has higher precedence than AND and OR.
• Use parentheses to group conditions clearly.
• Use IN for multiple alternatives on the same column.
• Use NOT IN to exclude listed values, but be cautious of NULL values.
• Use BETWEEN for inclusive ranges.
• Use LIKE and NOT LIKE for text-pattern matching.
• Use IS NULL and IS NOT NULL for missing-value checks.
• WHERE selects rows only when its condition evaluates to TRUE.
• Test filtering conditions with SELECT before applying them to UPDATE or DELETE.
• Use EXPLAIN and suitable indexes to investigate performance when needed.

29. Final Checklist

• Can you explain the difference between AND, OR, and NOT?
• Can you write a query that requires two or more conditions to be true?
• Can you retrieve records matching one of several alternatives?
• Can you exclude selected values using NOT or NOT IN?
• Can you combine AND and OR using parentheses?
• Can you explain operator precedence?
• Can you correctly filter NULL values?
• Can you use logical operators with comparison operators, BETWEEN, IN, and LIKE?
• Can you validate a query before using it to update or delete records?

30. Conclusion

AND, OR, and NOT are essential logical operators in MySQL that allow developers and database professionals to filter records using precise conditions. AND is used when all requirements must be met, OR is used when alternative conditions are acceptable, and NOT is used to exclude or negate a condition.

Understanding operator precedence, using parentheses, and handling NULL values correctly are especially important when writing queries that combine several conditions. Regular practice with employee, customer, product, and sales datasets helps build confidence in writing accurate and maintainable SQL queries.

To learn more about database concepts and practical SQL skills, explore the MySQL Training Course. You can also register for a course demo to learn more about the training.